﻿package 
{
	import flash.events.SQLEvent;
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	import flash.events.MouseEvent;
	import flash.data.SQLResult;
	import flash.events.EventDispatcher;
	import flash.events.Event;
	
	public class Util extends EventDispatcher
	{
		private var conn:SQLConnection;
		private var sql:SQLStatement;
		public var res:Array = new Array();
		
		public function Util()
		{
			//create a new sql connection
			conn = new SQLConnection();
			//add an event handeler for the open event
			conn.addEventListener(SQLEvent.OPEN, createTable);
			//create the database if it doesn't exist, otherwise just opens it
			var dbFile:File=File.applicationDirectory.resolvePath("db.db");
			conn.openAsync(dbFile);
			
		}

		private function createTable(event:SQLEvent):void {
			//create a new sql statement
			sql = new SQLStatement();
			//set the statement to connect to our database
			sql.sqlConnection=conn;
			//parse the sql command that creates the table if it doesn't exist
			sql.text= "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)";
			
			//add a new event listener to the sql when it completes creating the table, table gemaakt dan ophalen data
			sql.addEventListener(SQLEvent.RESULT,retrieveData);
			//call the execute function to execute our statement
			sql.execute();
			
		}
		
		public function add(n:String):void
		{
			// The statement (notice the words with :).
			sql.text = "INSERT INTO users (name) VALUES (:name)";
			
			// Set the paramenters.
			sql.parameters[":name"] = n;
			sql.execute();
			sql.clearParameters(); // Better clear the parameters if we are going to reuse the statement.
			dispatchEvent(new Event("ADDED"));
		}
		
		public function retrieveData(event:SQLEvent = null):void {
			//create a new sql statemant
			sql = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			sql.text =  "SELECT name FROM users";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			sql.addEventListener(SQLEvent.RESULT, selectHandler);
			sql.execute();
			
		}

		public function selectHandler(event:SQLEvent):void {
			
			res = event.target.getResult().data;
			if (res!=null) {
					//and we add a new item to our list for
					//each contact in our database
					for (var i:Number = 0; i < res.length; i++) {
						//trace(res[i].name);
					}
				}
			
			dispatchEvent(new Event("COMPLETED"));
			
		}	
					
		public function getData():Array
		{
			return res;
		}
		
		


	}

}